package Mysql;

import Business.Room;
import Message.Tips;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库操作类
 */
public class Update {

    /**
     * 设置房间音乐播放时间
     */
    public static boolean updRoomMusicDate(String room , boolean isNull){
        java.util.Date startDate = new java.util.Date();
        String sql;
        if (isNull){
            sql = "UPDATE room_base_" + room + " SET updDate = NOW() , musicDate = null;";
        }else{
            sql = "UPDATE room_base_" + room + " SET updDate = NOW() , musicDate = NOW();";
        }
        Connection connection = Mysql.Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            if(statement.executeUpdate(sql) < 1) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间歌曲播放时间更新(" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间歌曲播放时间更新(" + room + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 删除房间音乐
     * @param room 房间号
     * @param id 音乐数据库id
     */
    public static boolean removeRoomMusic(String room , int id){
        java.util.Date startDate = new java.util.Date();
        String sql;
        Connection connection = Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            sql = "DELETE FROM room_music_" + room + " WHERE id = " + id;
            if(statement.executeUpdate(sql) < 1) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间(" + room + ")歌曲(" + id + ")删除失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间(" + room + ")歌曲(" + id + ")删除成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 初始化房间
     * @return
     */
    public static boolean initRoom(){
        java.util.Date startDate = new java.util.Date();
        String sql;
        Connection connection = Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            int maxRoom = Room.maxRoomSize;
            for (int i = 0 ; i < maxRoom ; i ++){
                String name = Room.toStrRoom(i);
                sql = "DROP TABLE IF EXISTS room_base_" + name + ",room_user_" + name + "," +
                        "room_music_" + name + ",room_Doomm_" + name + ";";
                statement.executeUpdate(sql);
            }
            sql = "UPDATE userinfo SET room = -1;";
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            Mysql.Start.reConnection();//重连数据库
            Tips.Info("房间初始化成功失败！");
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间初始化成功成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 更新用户信息
     */
    public static boolean updUserInfo(String openid , String cookie , String nickName , String avatarUrl){
        java.util.Date startDate = new java.util.Date();
        String sql = "UPDATE userinfo SET nickName = ? , headimg = ? WHERE openid = ? and cookie = ?;";
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 ,nickName);
            preparedStatement.setString(2 ,avatarUrl);
            preparedStatement.setString(3 ,openid);
            preparedStatement.setString(4 ,cookie);
            if (preparedStatement.executeUpdate() < 1) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("更新用户数据(" + openid + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("更新用户数据(" + openid + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 更新用户cookie
     */
    public static String updCookie(String openid){
        java.util.Date startDate = new java.util.Date();
        String sql = "UPDATE userinfo SET  cookie = ? , updDate = NOW() WHERE openid = ?;";
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        String cookie = Business.Cookie.getCookie();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 ,cookie);
            preparedStatement.setString(2 ,openid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("更新Cookie(" + openid + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return null;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("更新Cookie(" + openid + ")成功！耗时：" + span + "毫秒");
        return cookie;
    }

    /**
     * 用户退出房间
     */
    public static boolean roomRemoveUser(String openid , String room){
        java.util.Date startDate = new java.util.Date();
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            String sql = "DELETE FROM room_user_" + room + " WHERE openid = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            preparedStatement.executeUpdate();
            sql = "UPDATE userinfo SET room = -1 WHERE openid = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            if(preparedStatement.executeUpdate() <= 0) return false;
            int roomCount = Mysql.Query.getRoomUserCount(room);
            if (roomCount <= 0){//删除存在房间
                sql = "DROP TABLE IF EXISTS room_base_" + room + ",room_user_" + room + "," +
                        "room_music_" + room + ",room_Doomm_" + room + ";";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("用户退出房间(" + openid + "|" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("用户退出房间(" + openid + "|" + room + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 房间添加音乐
     * @param room 房间号
     * @param musicId 网易云音乐id
     * @param name 音乐名称
     * @param artists 作者
     * @param duration 音乐时间 毫秒
     * @param imageUrl 封面url
     * @return 状态
     */
    public static boolean roomAddMusic(String room,String musicId,String name,
                                       String artists,int duration,String imageUrl,String url,boolean vip){
        java.util.Date startDate = new java.util.Date();
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            String sql = "INSERT INTO room_music_" + room + "(`musicId`,`name`,`artists`,`duration`,`imageUrl`,`url`,`vip`,`regDate`)" +
                    " VALUES(?,?,?,?,?,?,?,NOW());";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , musicId);
            preparedStatement.setString(2 , name);
            preparedStatement.setString(3 , artists);
            preparedStatement.setInt(4 , duration);
            preparedStatement.setString(5 , imageUrl);
            preparedStatement.setString(6 , url);
            preparedStatement.setBoolean(7 , vip);
            if(preparedStatement.executeUpdate() <= 0) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间(" + room + ")添加音乐失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间(" + room + ")添加音乐成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 房间加入用户
     */
    public static boolean roomAddUser(String openid,String room){
        java.util.Date startDate = new java.util.Date();
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            String sql = "REPLACE INTO room_user_" + room + "(`openid`, `joinDate`) VALUES(?, NOW());";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            if(preparedStatement.executeUpdate() <= 0) return false;
            sql = "UPDATE userinfo SET room = ? WHERE openid = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1 , Integer.parseInt(room));
            preparedStatement.setString(2 , openid);
            if(preparedStatement.executeUpdate() <= 0) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("用户加入房间(" + openid + "|" + room + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("用户加入房间(" + openid + "|" + room + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 注册用户
     */
    public static boolean addRoomDoomm(String room,String openid,String nickname,String content){
        java.util.Date startDate = new java.util.Date();
        String sql = "INSERT INTO room_Doomm_" + room + "(" +
                "`openid`,`nickName`,`content`,`regDate`) " +
                "VALUES (?,?,?,NOW());";
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            preparedStatement.setString(2 , nickname);
            preparedStatement.setString(3 , content);
            if(preparedStatement.executeUpdate() <= 0) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("房间(" + room + ")添加弹幕失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("房间(" + room + ")添加弹幕成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 注册用户
     */
    public static boolean addUser(String openid,String cookie){
        java.util.Date startDate = new java.util.Date();
        String sql = "INSERT INTO userinfo(" +
                "`openid`,`cookie`,`regDate`,`updDate`) " +
                "VALUES (?,?,NOW(),NOW());";
        Connection connection = Mysql.Start.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1 , openid);
            preparedStatement.setString(2 , cookie);
            if(preparedStatement.executeUpdate() <= 0) return false;
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("首次添加用户(" + openid + ")失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        java.util.Date endDate = new java.util.Date();
        long span = endDate.getTime() - startDate.getTime();
        Tips.Info("首次添加用户(" + openid + ")成功！耗时：" + span + "毫秒");
        return true;
    }

    /**
     * 删除房间
     * @param name 房间名称
     * @return 完成状态
     */
    public static boolean deleteRoom(String name){
        String sql;
        Connection connection = Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            sql = "DROP TABLE IF EXISTS room_base_" + name + ",room_user_" + name + "," +
                    "room_music_" + name + ",room_Doomm_" + name + ";";
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("初始化数据库失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        return true;
    }

    /**
     * 创建房间
     * @param name 房间名称
     * @return 完成状态
     */
    public static boolean createRoom(String name){
        String sql;
        Connection connection = Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            sql = "CREATE TABLE IF NOT EXISTS room_base_" + name + "(" +
                    "\nregDate DATETIME," +
                    "\nupdDate DATETIME," +
                    "\nmusicDate DATETIME" +
                    "\n)engine=InnoDB AUTO_INCREMENT=0 default charset=utf8;";
            statement.executeUpdate(sql);
            sql = "CREATE TABLE IF NOT EXISTS room_user_" + name + "(" +
                    "\nopenid VARCHAR(32) NOT NULL PRIMARY KEY," +
                    "\njoinDate DATETIME" +
                    "\n)engine=InnoDB AUTO_INCREMENT=0 default charset=utf8;";
            statement.executeUpdate(sql);
            sql = "CREATE TABLE IF NOT EXISTS room_music_" + name + "(" +
                    "\nid INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                    "\nmusicId VARCHAR(16) DEFAULT \"\"," +
                    "\nname VARCHAR(32) DEFAULT \"\"," +
                    "\nartists VARCHAR(64) DEFAULT \"\"," +
                    "\nduration INT DEFAULT -1," +
                    "\nimageUrl VARCHAR(256) DEFAULT \"\"," +
                    "\nurl VARCHAR(256) DEFAULT \"\"," +
                    "\nvip BOOLEAN DEFAULT true," +
                    "\nregDate DATETIME" +
                    "\n)engine=InnoDB AUTO_INCREMENT=0 default charset=utf8;";
            statement.executeUpdate(sql);
            sql = "CREATE TABLE IF NOT EXISTS room_Doomm_" + name + "(" +
                    "\nid INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                    "\nopenid VARCHAR(32) NOT NULL," +
                    "\nnickName VARCHAR(128) DEFAULT \"\"," +
                    "\ncontent VARCHAR(256) DEFAULT \"\"," +
                    "\nregDate DATETIME" +
                    "\n)engine=InnoDB AUTO_INCREMENT=0 default charset=utf8;";
            statement.executeUpdate(sql);
            sql = "INSERT INTO room_base_" + name + "(`regDate`,`updDate`)" +
                    " VALUES(NOW(),NOW());";
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("初始化数据库失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        return true;
    }

    /**
     * 初始化数据库
     */
    public static boolean initCreateTable(){
        String sql;
        Connection connection = Start.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            sql = "CREATE TABLE IF NOT EXISTS userinfo(" +
                    "\nid INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
                    "\nopenid VARCHAR(32)," +
                    "\nnickName VARCHAR(128) DEFAULT \"神秘人\"," +
                    "\nheadimg VARCHAR(256) DEFAULT \"\"," +
                    "\nroom INT(8) DEFAULT -1," +
                    "\ncookie VARCHAR(32) DEFAULT \"\"," +
                    "\nregDate DATETIME," +
                    "\nupdDate DATETIME" +
                    "\n)engine=InnoDB AUTO_INCREMENT=0 default charset=utf8;";
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            Tips.Warm("初始化数据库失败！");
            Mysql.Start.reConnection();//重连数据库
            return false;
        }finally {
            try {
                if(statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                Tips.Warm("Statement关闭失败！");
            }
        }
        return true;
    }

}
